Database management systems provide for stored procedures or user defined functions (user-defined stored routines) to be invoked from applications using the database systems. Typically, an application will use the database system to invoke a routine during a defined transaction. In database management systems such as the DB2 UDB J product, when a user-defined routine is invoked a separate process is launched by the database system to execute the routine. The isolation of execution of user-defined routines in processes separate from the database system itself prevents memory corruption or traps in user-written routines from affecting the database engine. There is also a separation between different user-defined routines that will prevent an erroneously coded user-defined routine from adversely affecting another.
There is a potentially high cost in system resources, however, that is associated with launching a new process for each user-defined routine that is invoked by the database system. This is particularly true for Java language stored procedures where a significant amount of memory is consumed by the presence of a Java virtual machine in each process, which is required to execute the Java code for the user-defined routine.
Another approach used in database systems such as DB2 UDB J is to generally restrict users from invoking stored routines from other stored routines (in some cases, such systems may make exceptions for a routine written in one of a defined set of languages). This restriction will prevent a user routine written in a language that limits the ability to erroneously effect another routine (Java language routines are considered “safe” in this way) from calling a routine that does not contain such built in limits (as, for example, the C language does not). The restriction will also prevent routines from being recursively called where the routine is not properly defined to be re-entrant.
It is therefore desirable to provide a mechanism to permit the invocation of stored routines that isolates potentially damaging routines from other routines without requiring a new process for every such invocation. It is also desirable to provide a mechanism that permits recursive invocation of stored routines without requiring restrictions based on the languages in which the routines were written.
The present invention addresses such a need.